﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace GestorDeFlotasDesktop.Listado
{
    public partial class Listado : Form
    {
        public Listado()
        {
            InitializeComponent();
            cb_tipo_listado.Items.Add("choferes");
            cb_tipo_listado.Items.Add("taxis");
            cb_tipo_listado.Items.Add("clientes");
            cb_trimestre.Items.Add("1");
            cb_trimestre.Items.Add("2");
            cb_trimestre.Items.Add("3");
            cb_trimestre.Items.Add("4");
        }

        private void Listado_Load(object sender, EventArgs e)
        {

        }

        private void cb_tipo_listado_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void aceptar_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = Modelo.Modelo.createConnection())
            {
                if (tb_anio.Text != "" && cb_trimestre.SelectedIndex.ToString() != "" && cb_tipo_listado.SelectedIndex.ToString() != "")
                {
                    string fecha_ini = "";
                    string fecha_fin = "";
                    string consulta = "";

                    if (cb_trimestre.SelectedItem.ToString() == "1")
                    {
                        fecha_ini = "convert(datetime, '" + tb_anio.Text + "-01-01 00:00:00', 120)";
                        fecha_fin = "convert(datetime, '" + tb_anio.Text + "-03-31 23:59:59', 120)";
                    }
                    if (cb_trimestre.SelectedItem.ToString() == "2")
                    {
                        fecha_ini = "convert(datetime, '" + tb_anio.Text + "-04-01 00:00:00', 120)";
                        fecha_fin = "convert(datetime, '" + tb_anio.Text + "-06-30 23:59:59', 120)";
                    }
                    if (cb_trimestre.SelectedItem.ToString() == "3")
                    {
                        fecha_ini = "convert(datetime, '" + tb_anio.Text + "-07-01 00:00:00', 120)";
                        fecha_fin = "convert(datetime, '" + tb_anio.Text + "-09-30 23:59:59', 120)";
                    }
                    if (cb_trimestre.SelectedItem.ToString() == "4")
                    {
                        fecha_ini = "convert(datetime, '" + tb_anio.Text + "-10-01 00:00:00', 120)";
                        fecha_fin = "convert(datetime, '" + tb_anio.Text + "-12-31 23:59:59', 120)";
                    }


                    //===============================================================================================
                    //0: choferes
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "0")
                    {
                        consulta += "SELECT TOP (5) Id_Chofer,(SELECT Ch_Dni FROM NUNCA_TAXI.Choferes WHERE (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS DNI,(SELECT Ch_Nombre FROM NUNCA_TAXI.Choferes AS Choferes_2 WHERE (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS NOMBRE,(SELECT Ch_Apellido FROM NUNCA_TAXI.Choferes AS Choferes_1 WHERE (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS APELLIDO, SUM(Vi_Importe) AS GastoTotal FROM NUNCA_TAXI.Viajes WHERE (Vi_Fecha BETWEEN ";
                        consulta += fecha_ini;
                        consulta += " AND ";
                        consulta += fecha_fin;
                        consulta += ") GROUP BY Id_Chofer ORDER BY gastototal DESC";

                        SqlDataAdapter da = new SqlDataAdapter(consulta , con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    //===============================================================================================
                    //1: taxis
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "1")
                    {
                        consulta += "SELECT TOP (5) Id_Taxi,(SELECT Ta_Patente FROM NUNCA_TAXI.Taxis WHERE (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)) AS patente,(SELECT Ta_Licencia FROM NUNCA_TAXI.Taxis AS Choferes_2 WHERE (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)) AS licencia,(SELECT Ma_NombreTaxi FROM NUNCA_TAXI.MarcasTaxi WHERE (Id_MarcaTaxi =(SELECT Id_MarcaTaxi FROM NUNCA_TAXI.Taxis AS Taxis_1 WHERE (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)))) AS MARCA, SUM(Vi_Importe) AS GastoTotal FROM NUNCA_TAXI.Viajes WHERE (Vi_Fecha BETWEEN ";
                        consulta += fecha_ini;
                        consulta += " AND ";
                        consulta += fecha_fin;
                        consulta += ")GROUP BY Id_Taxi ORDER BY gastototal DESC";

                        SqlDataAdapter da = new SqlDataAdapter(consulta, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    //===============================================================================================
                    //2: clientes
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "2")
                    {
                        consulta += "SELECT TOP (5) Id_Cliente,(SELECT Cl_Dni FROM NUNCA_TAXI.Clientes WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS DNI,(SELECT Cl_Nombre FROM NUNCA_TAXI.Clientes AS Clientes_2 WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS NOMBRE,(SELECT Cl_Apellido FROM NUNCA_TAXI.Clientes AS Clientes_1 WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS APELLIDO, SUM(Vi_Importe) AS GastoTotal FROM NUNCA_TAXI.Viajes WHERE (Id_Cliente != 1) AND (Vi_Fecha BETWEEN ";
                        consulta += fecha_ini;
                        consulta += " AND ";
                        consulta += fecha_fin;
                        consulta += ") GROUP BY Id_Cliente ORDER BY gastototal DESC";

                        SqlDataAdapter da = new SqlDataAdapter(consulta, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }
                else
                {
                    MessageBox.Show("Por favor, complete todos los campos para continuar.");
                }
            }
        }
    }
}
